package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.UserEntity;
import com.b2c.entity.erp.*;
import com.b2c.entity.erp.enums.ErpOrderSourceEnum;
import com.b2c.entity.erp.vo.ExpressInfoVo;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.enums.erp.EnumErpOrderStatus;
import com.b2c.entity.enums.erp.EnumErpSalesOrderRefundStatus;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;
import com.b2c.repository.Tables;
import com.b2c.entity.vo.erp.ErpSalesOrderDetailVo;
import com.b2c.entity.vo.erp.ErpSalesOrderRefundDetailVo;
import com.b2c.entity.vo.erp.ErpSalesOrderRefundItemVo;
import com.b2c.entity.vo.OrderRefundApplyVo;
import com.b2c.entity.vo.OrderImportDaiFaEntity;
import com.b2c.entity.vo.OrderImportPiPiEntity;
import com.b2c.entity.ErpOrderReturnEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 销售外发订单
 */
@Repository
public class ErpSalesOrderRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static Logger log = LoggerFactory.getLogger(ErpSalesOrderRepository.class);
    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    public ErpSalesOrderEntity getById(Long orderId) {
        try {
            return jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpSalesOrder + " WHERE id=?", new BeanPropertyRowMapper<>(ErpSalesOrderEntity.class), orderId);
        } catch (Exception e) {
            return null;
        }
    }

    @Transactional
    public PagingResponse<ErpSalesOrderEntity> getList(int pageIndex, int pageSize, String orderNum, String contactMobile, Integer saleType, Integer clientUserId, Integer status,Integer shopId,Integer auditStatus, Integer startTime, Integer endTime) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS o.*,dev.name as developerName,shop.name as shopName");
        sb.append(",IFNULL((SELECT SUM(refundCount) FROM ").append(Tables.ErpSalesOrderItem).append(" WHERE orderId=o.id),0) AS refundQuantity ");
        sb.append(" FROM ").append(Tables.ErpSalesOrder).append(" as o ");
        sb.append(" LEFT JOIN ").append(Tables.DcShop).append(" as shop on shop.id = o.shopId ");
        sb.append(" LEFT JOIN ").append(Tables.ErpContact).append("  as dev on dev.id = o.buyerUserId  ");
        sb.append(" WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (saleType != null && saleType>=0) {
            sb.append(" AND o.saleType=? ");
            params.add(saleType);
        }
        if (shopId != null && shopId>0) {
            sb.append(" AND o.shopId=? ");
            params.add(shopId);
        }
        if (auditStatus != null && auditStatus>=0) {
            if(auditStatus.intValue() == 0){
                //待确认的订单，不查询已取消的
                sb.append(" AND o.auditStatus=? AND status != -1 ");
                params.add(auditStatus);
            }else {
                sb.append(" AND o.auditStatus=? ");
                params.add(auditStatus);
            }
        }

        if (clientUserId != null) {
            sb.append(" AND o.buyerUserId=? ");
            params.add(clientUserId);
        }
        if (status != null) {
            sb.append(" AND o.status=? ");
            params.add(status);
        }
        if (startTime != null && startTime > 0) {
            sb.append(" AND o.createOn >= ? ");
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sb.append(" AND o.createOn <= ? ");
            params.add(endTime);
        }
        if (StringUtils.isEmpty(orderNum) == false) {
            sb.append(" AND o.orderNum=? ");
            params.add(orderNum);
        }

        if (StringUtils.isEmpty(contactMobile) == false) {
            sb.append(" AND o.contactMobile=? ");
            params.add(contactMobile);
        }

        sb.append(" ORDER BY o.id DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpSalesOrderEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }


    /**
     * 查询订单商品
     * @param pageIndex
     * @param pageSize
     * @param orderNum
     * @param sku
     * @param clientUserId
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Transactional
    public PagingResponse<ErpSalesOrderItemView> getOrderItemList(int pageIndex, int pageSize, String orderNum, String sku, Integer clientUserId, Integer shopId, Integer startTime, Integer endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT SQL_CALC_FOUND_ROWS ");

        sql.append(" so.id as orderId,so.orderNum,so.buyerUserId,so.buyerName,so.goodsTotalAmount,so.shippingFee,so.contactPerson,so.contactMobile,so.address,so.saleType,");
        sql.append("so.createOn,so.status,so.logisticsCompany,so.logisticsCode,so.payStatus,so.deliveredStatus,");
        sql.append("soi.itemAmount,soi.goodsId,soi.goodsNumber,soi.goodsTitle,soi.goodsImage,soi.skuInfo,");
        sql.append("soi.specId,soi.specNumber,soi.quantity,soi.price,soi.refundCount,soi.refundId,soi.isGift ");
        sql.append(" FROM erp_sales_order_item soi ");
        sql.append(" LEFT JOIN erp_sales_order so ON so.id = soi.orderId ");
        sql.append(" WHERE so.shopId=? ");
        List<Object> params = new ArrayList<>();
        params.add(shopId);
        if(clientUserId !=null && clientUserId >0){
            sql.append(" AND so.buyerUserId=? ");
            params.add(clientUserId);
        }
        if (startTime != null && startTime > 0) {
            sql.append(" AND so.createOn >= ? ");
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sql.append(" AND so.createOn <= ? ");
            params.add(endTime);
        }
        if (StringUtils.isEmpty(orderNum) == false) {
            sql.append(" AND so.orderNum=? ");
            params.add(orderNum);
        }
        if (StringUtils.isEmpty(sku) == false) {
            sql.append(" AND soi.specNumber=? ");
            params.add(sku);
        }

        sql.append(" ORDER BY so.id DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ErpSalesOrderItemView.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }



    public PagingResponse<ErpSalesOrderRefundEntity> getRefundList(int pageIndex, int pageSize, String refundNum, String orderNum, String logisticsCode, Integer saleType, Integer clientUserId, Integer status, Integer startTime, Integer endTime) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS o.* ");
        sb.append(",IFNULL((SELECT SUM(quantity) FROM erp_sales_order_refund_item WHERE refundId=o.id),0) AS totalRefund ");
        sb.append(",(select name from dc_shop where id=o.shopId ) shopName ");
        sb.append(",IFNULL((SELECT SUM(refundAmount) FROM erp_sales_order_refund_item WHERE refundId=o.id),0) AS totalRefundAmount ");

        sb.append(" FROM ").append(Tables.ErpSalesOrderRefund).append(" as o ");
        sb.append(" WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (saleType != null) {
            sb.append(" AND o.orderSaleType=? ");
            params.add(saleType);
        }
        if (clientUserId != null) {
            sb.append(" AND o.buyerUserId=? ");
            params.add(clientUserId);
        }
        if (status != null) {
            sb.append(" AND o.status=? ");
            params.add(status);
        }
        if (startTime != null && startTime > 0) {
            sb.append(" AND o.createOn >= ? ");
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sb.append(" AND o.createOn <= ? ");
            params.add(endTime);
        }
        if (StringUtils.isEmpty(orderNum) == false) {
            sb.append(" AND o.orderNum=? ");
            params.add(orderNum);
        }
        if (StringUtils.isEmpty(refundNum) == false) {
            sb.append(" AND o.refundNum=? ");
            params.add(refundNum);
        }

        if (StringUtils.isEmpty(logisticsCode) == false) {
            sb.append(" AND o.logisticsCode=? ");
            params.add(logisticsCode);
        }

        sb.append(" ORDER BY o.id DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpSalesOrderRefundEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }



    /**
     * 导入代发订单
     *
     * @param orderList
     * @param buyerUserId
     * @return
     */
    @Transactional
    public ResultVo<String> importExcelOrderForDaiFa(List<OrderImportDaiFaEntity> orderList, Integer buyerUserId) {
        log.info("导入代发订单importExcelOrderForDaiFa");
        if (orderList == null || orderList.size() == 0)
            return new ResultVo<>(EnumResultVo.DataError, "参数错误：缺少orderList","");
        if (buyerUserId == null || buyerUserId == 0)
            return new ResultVo<>(EnumResultVo.DataError, "参数错误：缺少buyerUserId","");

        UserEntity buyerUser = new UserEntity();
        try {
            //查询客户数据
            buyerUser = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.User + " WHERE id=?", new BeanPropertyRowMapper<>(UserEntity.class), buyerUserId);
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataError, "参数错误：buyerUserId找不到客户","");
        }

        /*******插入数据********/
        int totalInsert = 0;//新增数量
        int totalExist = 0;//已存在数量
        int totalError = 0;//错误数量
        for (var order : orderList) {
            //查询订单是否存在
            var oList = jdbcTemplate.query("SELECT * FROM " + Tables.ErpSalesOrder + " WHERE orderNum=? ", new BeanPropertyRowMapper<>(OrderImportPiPiEntity.class), order.getId());

            if (oList != null && oList.size() > 0) {
                //已经存在
                totalExist++;
            } else {

                try {
                    /**************1、新增order**********************/
                    //商品总价
                    double goodsTotalAmount = 0.0;
                    //商品总数
                    long goodsCount = 0;
                    //sku总数
                    long goodsSpecCount = 0;

                    for (var item : order.getItems()) {
                        goodsCount += item.getQuantity();
                        goodsSpecCount++;
                        goodsTotalAmount += item.getPrice().doubleValue() * item.getQuantity();
                    }
                    //销售类型为实售
                    int saleType = 1;
                    //订单状态为待发货
                    int status = EnumErpOrderStatus.WaitSend.getIndex();

                    StringBuilder insertSQL = new StringBuilder();
                    insertSQL.append("INSERT INTO ").append(Tables.ErpSalesOrder);
                    insertSQL.append(" (orderNum,buyerUserId,buyerName,goodsCount,goodsSpecCount,goodsTotalAmount,shippingFee,totalAmount,buyerFeedback,sellerMemo,contactPerson,contactMobile,province,city,area,address," +
                            "saleType,status,createOn,createBy,modifyOn,developerId,source,orderTime,billMethod)");
                    insertSQL.append(" VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1) ");

                    KeyHolder keyHolder = new GeneratedKeyHolder();
                    UserEntity finalBuyerUser = buyerUser;
                    long finalGoodsSpecCount = goodsSpecCount;
                    long finalGoodsCount = goodsCount;
                    double finalGoodsTotalAmount = goodsTotalAmount;
                    jdbcTemplate.update(new PreparedStatementCreator() {
                        @Override
                        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                            PreparedStatement ps = connection.prepareStatement(insertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                            ps.setString(1, order.getId());
                            ps.setInt(2, buyerUserId);
                            ps.setString(3, finalBuyerUser.getUserName());
                            ps.setLong(4, finalGoodsCount);
                            ps.setLong(5, finalGoodsSpecCount);
                            ps.setBigDecimal(6, new BigDecimal(finalGoodsTotalAmount));
                            ps.setBigDecimal(7, new BigDecimal(0));
                            ps.setBigDecimal(8, new BigDecimal(finalGoodsTotalAmount));
                            ps.setString(9, order.getBuyerFeedback());
                            ps.setString(10, order.getSellerMemo());
                            ps.setString(11, order.getContactPerson());
                            ps.setString(12, order.getContactMobile());
                            ps.setString(13, order.getProvince() != null ? order.getProvince() : "");
                            ps.setString(14, order.getCity() != null ? order.getCity() : "");
                            ps.setString(15, order.getArea() != null ? order.getArea() : "");
                            ps.setString(16, order.getAddress() != null ? order.getAddress() : "");
                            ps.setInt(17, saleType);
                            ps.setInt(18, status);
                            ps.setLong(19, System.currentTimeMillis() / 1000);
                            ps.setString(20, "OMS-import-DaiFa");
                            ps.setLong(21, System.currentTimeMillis() / 1000);
                            ps.setInt(22, finalBuyerUser.getDeveloperId());
                            ps.setString(23, "DaiFaImport");
                            ps.setLong(24, DateUtil.dateToStamp(order.getOrderTime()));
                            return ps;
                        }
                    }, keyHolder);

                    Long orderId = keyHolder.getKey().longValue();

                    /*******************2、添加order_item**************************/
                    //添加订单明细
                    String itemSQL = "INSERT INTO " + Tables.ErpSalesOrderItem + " (orderId,itemAmount,goodsId,goodsTitle,goodsNumber,goodsImage,skuInfo,specId,specNumber,color,size,price,quantity,discountPrice) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    for (var item : order.getItems()) {
                        double itemAmount = item.getPrice().doubleValue() * item.getQuantity();
                        jdbcTemplate.update(itemSQL, orderId, itemAmount, item.getGoodsId(), item.getGoodsTitle(), item.getGoodsNumber(), item.getGoodsImg(),
                                item.getSkuInfo(), item.getSpecId() == null ? 0 : item.getSpecId(), item.getSpecNumber(), item.getColor(), item.getSize(), item.getPrice(), item.getQuantity(), 0);
                    }

                    totalInsert++;//新增成功

                } catch (Exception e) {
                    totalError++;
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.SystemException, "系统异常：" + e.getMessage(),"");
                }
            }
        }
        String msg = "新增成功：" + totalInsert + "，新增失败：" + totalError + "，已存在：" + totalExist;
////        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", msg);
//    }
    }


    public ErpSalesOrderDetailVo getDetailById(Long orderId) {
        try {
            String detailSQL = "SELECT o.*,sh.name as shopName FROM " + Tables.ErpSalesOrder + " AS o LEFT JOIN "+Tables.DcShop+" as sh on sh.id=o.shopId WHERE o.id=?";
            var order = jdbcTemplate.queryForObject(detailSQL, new BeanPropertyRowMapper<>(ErpSalesOrderDetailVo.class), orderId);

            order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.ErpSalesOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class), orderId));
            return order;
        } catch (Exception e) {
            return null;
        }
    }

    public ErpSalesOrderItemEntity getOrderItemByItemId(Long orderItemId) {
        try {
            String detailSQL = "SELECT * FROM " + Tables.ErpSalesOrderItem + " WHERE id=?";
            return jdbcTemplate.queryForObject(detailSQL, new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class), orderItemId);
        } catch (Exception e) {
            return null;
        }

    }


    public ErpSalesOrderRefundDetailVo getRefundDetailById(Long refundId) {
        try {
            var sql = "SELECT ref.* FROM " + Tables.ErpSalesOrderRefund + " as ref  WHERE ref.id=?";

            var order = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpSalesOrderRefundDetailVo.class), refundId);

            String itemSQL = "SELECT ri.*,oi.goodsNumber,oi.goodsTitle,oi.goodsImage,oi.color,oi.size,oi.specNumber,oi.quantity as buyQuantity FROM " + Tables.ErpSalesOrderRefundItem + " AS ri" +
                    " LEFT JOIN " + Tables.ErpSalesOrderItem + " as oi ON oi.id=ri.orderItemId" +
                    " WHERE ri.refundId=?";
            order.setItems(jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(ErpSalesOrderRefundItemVo.class), refundId));
            return order;
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 更新orderItem 商品规格
     *
     * @param orderItem
     */
    public void updateOrderItemSkuByItemId(ErpSalesOrderItemEntity orderItem) {
        try {
            if (orderItem != null) {
                String sql = "UPDATE " + Tables.ErpSalesOrderItem + " SET goodsImage=?,color=?,size=?,specNumber=?,specId=?,skuInfo=?,modifySkuRemark=? WHERE id=?";
                jdbcTemplate.update(sql, orderItem.getGoodsImage(), orderItem.getColor(), orderItem.getSize(), orderItem.getSpecNumber(), orderItem.getSpecId()
                        ,orderItem.getSkuInfo(),orderItem.getModifySkuRemark(), orderItem.getId());
            }
        } catch (Exception e) {
            throw e;
        }
    }
    /**
     * 删除orderItem 商品
     *
     */
    public void delOrderItemSkuByItemId(Long orderId, Long itemId) {
        try {
            String sql = "delete from " + Tables.ErpSalesOrderItem + "  WHERE id=?";
            jdbcTemplate.update(sql, itemId);

            var order = getDetailById(orderId);
            Long goodCount=0L;
            double amount=0d;
            for(var item:order.getItems()){
                goodCount=goodCount+item.getQuantity();
                amount=amount+item.getPrice().doubleValue();
            }
            jdbcTemplate.update("update erp_sales_order set goodsTotalAmount=?,goodsCount=?,goodsSpecCount=?,totalAmount=? where id=?",amount,goodCount,goodCount,amount+order.getShippingFee().doubleValue(),orderId);
        } catch (Exception e) {
            throw e;
        }
    }


    /**
     * 申请退货
     *
     * @param applyVo
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> refundApply(OrderRefundApplyVo applyVo) {
        if (applyVo.getOrderId() == null) return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，没有订单id");
        if (applyVo.getReturnItems() == null || applyVo.getReturnItems().size() == 0)
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误,没有选择要退货的商品");

        ErpSalesOrderEntity order = new ErpSalesOrderEntity();
        try {
            order = jdbcTemplate.queryForObject("select * from " + Tables.ErpSalesOrder + " where id=?", new BeanPropertyRowMapper<>(ErpSalesOrderEntity.class), applyVo.getOrderId());
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误,订单不存在");
        }
        if (order.getStatus() < EnumErpOrderStatus.Delivered.getIndex() || order.getStatus() > EnumErpOrderStatus.Completed.getIndex()) {
            return new ResultVo<>(EnumResultVo.ParamsError, "订单未发货或者订单已完成");
        }

        //组合后的退货orderItem list
//        List<ErpSalesOrderItemEntity> returnOrderItems = new ArrayList<>();


//        int returnTotal = 0; //本次勾选的退货总数
//        try {
//            /*****************判断退货数量信息******************/
//            for (var returnItem : applyVo.getReturnItems()) {
//                var item = jdbcTemplate.queryForObject("select * from " + Tables.ErpSalesOrderItem + " where id=?", new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class), returnItem.getOrderItemId());
//
//                /*******计算可退货数量********/
//                int canReturnCount = item.getQuantity() - item.getRefundCount().intValue();
//                int returnCount = returnItem.getReturnCount();//本次退货数量
//
//                if (canReturnCount < returnCount) {
//                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                    return new ResultVo<>(EnumResultVo.DataError, item.getSpecNumber() + "退货数量超过可退数量");
//                }
//
//                if (returnCount > 0) {
////                    //条件通过，将退货数量字段设置为当前退货数量
////                    item.setRefundCount(returnCount);
////
////                    //数量不为0的才加入退货单
////                    returnOrderItems.add(item);
//                    returnTotal += returnCount;
//                }
//            }
//        } catch (Exception e) {
//            return new ResultVo<>(EnumResultVo.DataError, e.getMessage());
//        }
//
//        if (returnTotal <= 0) return new ResultVo<>(EnumResultVo.DataError, "退货数量为0，无需提交");


        /******************开始退货数据库操作*********************/
        ErpSalesOrderEntity finalOrder = order;

        try {
            String refundNo = "SALRET" + System.nanoTime();//退货单号

            String refundSQL = "INSERT INTO " + Tables.ErpSalesOrderRefund + " SET refundNum=?,orderId=?,orderNum=?,orderSaleType=?,buyerUserId=?,refundApplyTime=unix_timestamp(now()),createOn=unix_timestamp(now()),status=?,type=0,shopId=?";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            //插入退货单主表

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(refundSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, refundNo);
                    ps.setLong(2, applyVo.getOrderId());
                    ps.setString(3, finalOrder.getOrderNum());
                    ps.setInt(4, finalOrder.getSaleType());
                    ps.setInt(5, finalOrder.getBuyerUserId());
                    ps.setInt(6, EnumErpSalesOrderRefundStatus.Applying.getIndex());
                    ps.setInt(7, finalOrder.getShopId());
                    return ps;
                }
            }, keyHolder);
            Long refundId = keyHolder.getKey().longValue();

            //插入退货单子表
            String refundItemSQL = "insert into " + Tables.ErpSalesOrderRefundItem + " set refundId=?,orderId=?,orderItemId=?,quantity=?,buyAmount=?,refundAmount=?";

            for (var returnItem : applyVo.getReturnItems()) {
//            for (int i = 0, n = returnOrderItems.size(); i < n; i++) {
//                var item = returnOrderItems.get(i);
//                int returnNumber = item.getQuantity();//Integer.parseInt(nums[i]);//本次退货数量
                var item = jdbcTemplate.queryForObject("select * from " + Tables.ErpSalesOrderItem + " where id=?", new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class), returnItem.getOrderItemId());

                /*******计算可退货数量********/
                int canReturnCount = item.getQuantity() - item.getRefundCount().intValue();
                int returnCount = returnItem.getReturnCount();//本次退货数量

                if (canReturnCount < returnCount) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.DataError, item.getSpecNumber() + "退货数量超过可退数量");
                }

                if (returnItem.getReturnCount() > 0) {

                    /*************添加退货明细*************/
                    jdbcTemplate.update(refundItemSQL, refundId, finalOrder.getId(), returnItem.getOrderItemId(), returnItem.getReturnCount()
                            ,item.getDiscountPrice().doubleValue() * returnCount,item.getDiscountPrice().doubleValue() * returnCount);


                    /*************更新orderitem数据 （售后数量）*************/
                    jdbcTemplate.update("update " + Tables.ErpSalesOrderItem + " set refundCount=refundCount+?,refundId=?,itemStatus=2 where id=?", returnItem.getReturnCount(), refundNo, returnItem.getOrderItemId());
                }
            }
//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SUCCESS, "申请成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, e.getMessage());
        }
    }


    /**
     * 同意退回
     *
     * @param refundId 退货id
     * @param exress   物流信息
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> refundAgree(Long refundId, ExpressInfoVo exress) {
        //查询退款单
        ErpSalesOrderRefundEntity refundOrder = null;
        try {
            refundOrder = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpSalesOrderRefund + " WHERE id=?", new BeanPropertyRowMapper<>(ErpSalesOrderRefundEntity.class), refundId);
            if (refundOrder.getStatus() == EnumErpSalesOrderRefundStatus.Cancel.getIndex())
                return new ResultVo<>(EnumResultVo.ParamsError, "退货申请已取消");
            else if (refundOrder.getStatus() == EnumErpSalesOrderRefundStatus.Received.getIndex())
                return new ResultVo<>(EnumResultVo.ParamsError, "退货已经收货");
            else if (refundOrder.getStatus() == EnumErpSalesOrderRefundStatus.Refuse.getIndex())
                return new ResultVo<>(EnumResultVo.ParamsError, "退货已经被拒绝");

            if(refundOrder.getType().intValue() != 0){
                //退款类型(0:退货退款，1仅退款）
                //类型非0的无需处理
                return new ResultVo<>(EnumResultVo.ParamsError, "仅退款的订单无需处理");
            }

            if (StringUtils.isEmpty(exress.getLogisticsCode()) && StringUtils.isEmpty(refundOrder.getLogisticsCode())) {
                return new ResultVo<>(EnumResultVo.ParamsError, "没有发货物流信息");
            }
        } catch (Exception e1) {
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单不存在");//订单不存在return 404;
        }

        ErpSalesOrderEntity order = null;
        try {
            //查询关联的订单
            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpSalesOrder + " WHERE id=?", new BeanPropertyRowMapper<>(ErpSalesOrderEntity.class), refundOrder.getOrderId());
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在return 404;
        }


        var returnOrderItem = jdbcTemplate.query("SELECT * FROM " + Tables.ErpSalesOrderRefundItem + " WHERE refundId=?", new BeanPropertyRowMapper<>(ErpSalesOrderRefundItemEntity.class), refundOrder.getId());
        if (returnOrderItem == null || returnOrderItem.size() == 0)
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单明细不存在");


        /**1.同意退货**/
//        Integer shopId = 99;

        /***********  查询仓库系统退货订单 是否存在 *************/
        var erpOrderReturn = jdbcTemplate.query("SELECT * FROM " + Tables.ErpOrderReturn + " WHERE order_num=? AND shopId=?", new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class), refundOrder.getRefundNum(), order.getShopId());
        if (erpOrderReturn != null && erpOrderReturn.size() > 0) {
            //退货单已经存在
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
        }

        /***********  插入仓库系统退款订单 erp_order_return    erp_order_return_item  *************/
        try {
            //插入仓库退货订单 erp_order_return
            String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                    " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)" +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";

            KeyHolder keyHolder = new GeneratedKeyHolder();


            ErpSalesOrderRefundEntity finalRefundOrder = refundOrder;


            ErpSalesOrderEntity finalOrder = order;
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, finalRefundOrder.getRefundNum());
                    ps.setLong(2, finalRefundOrder.getCreateOn());
                    ps.setLong(3, System.currentTimeMillis() / 1000);
                    ps.setString(4, finalOrder.getContactPerson());
                    ps.setString(5, finalOrder.getContactMobile());
                    ps.setString(6, finalOrder.getAddress());
                    ps.setString(7, ErpOrderSourceEnum.Sales.getIndex());
                    ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                    ps.setString(9, StringUtils.isEmpty(exress.getLogisticsCompany()) ? finalRefundOrder.getLogisticsCompany() : exress.getLogisticsCompany());
                    ps.setString(10, StringUtils.isEmpty(exress.getLogisticsCompanyCode()) ? finalRefundOrder.getLogisticsCompanyCode() : exress.getLogisticsCompanyCode());
                    ps.setString(11, StringUtils.isEmpty(exress.getLogisticsCode()) ? finalRefundOrder.getLogisticsCode() : exress.getLogisticsCode());
                    ps.setString(12, finalOrder.getOrderNum());
                    ps.setLong(13, finalOrder.getShopId());
                    return ps;
                }
            }, keyHolder);
            Long returnOrderId = keyHolder.getKey().longValue();

            //插入仓库退货订单明细 erp_order_return_item
            String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,orderItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?)";

            for (var ritem : returnOrderItem) {
                //查询订单明细
                var orderItem = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpSalesOrderItem + " WHERE id=?", new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class), ritem.getOrderItemId());
                //查询仓库系统sku
                var sku = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), orderItem.getSpecId());

                Long orderItemId =jdbcTemplate.queryForObject("SELECT IFNULL((SELECT i.id FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",Long.class,order.getOrderNum(),sku.getId());

                /******插入仓库系统退货商品信息*******/
                jdbcTemplate.update(returnOrderItemSQL, returnOrderId, sku.getGoodsId(), sku.getId(), sku.getSpecNumber(), ritem.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,orderItemId);
            }


            /**************更新退货订单信息 erp_sales_order_refund*****************/
            jdbcTemplate.update("UPDATE " + Tables.ErpSalesOrderRefund + " SET logisticsCompany=?,logisticsCompanyCode=?,logisticsCode=?,status=? WHERE id =?",
                    StringUtils.isEmpty(exress.getLogisticsCompany()) ? refundOrder.getLogisticsCompany() : exress.getLogisticsCompany(),
                    StringUtils.isEmpty(exress.getLogisticsCompanyCode()) ? refundOrder.getLogisticsCompanyCode() : exress.getLogisticsCompanyCode(),
                    StringUtils.isEmpty(exress.getLogisticsCode()) ? refundOrder.getLogisticsCode() : exress.getLogisticsCode(),
                    EnumErpSalesOrderRefundStatus.Delivered.getIndex(), refundId);


            // TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "同意退货处理失败"+e.getMessage());
        }


    }

    /**
     * 退款拒绝
     *
     * @param refundId
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> refundRefuse(Long refundId) {
        //查询退款单
        ErpSalesOrderRefundEntity refundOrder = null;
        try {
            refundOrder = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpSalesOrderRefund + " WHERE id=?", new BeanPropertyRowMapper<>(ErpSalesOrderRefundEntity.class), refundId);
            if (refundOrder.getStatus() == EnumErpSalesOrderRefundStatus.Cancel.getIndex())
                return new ResultVo<>(EnumResultVo.ParamsError, "退货申请已取消");
            else if (refundOrder.getStatus() != EnumErpSalesOrderRefundStatus.Applying.getIndex())
                return new ResultVo<>(EnumResultVo.ParamsError, "退货申请已处理");

        } catch (Exception e1) {
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单不存在");//订单不存在return 404;
        }

        ErpSalesOrderEntity order = null;
        try {
            //查询关联的订单
            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpSalesOrder + " WHERE id=?", new BeanPropertyRowMapper<>(ErpSalesOrderEntity.class), refundOrder.getOrderId());
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在return 404;
        }


        var returnOrderItem = jdbcTemplate.query("SELECT * FROM " + Tables.ErpSalesOrderRefundItem + " WHERE refundId=?", new BeanPropertyRowMapper<>(ErpSalesOrderRefundItemEntity.class), refundOrder.getId());
        if (returnOrderItem == null || returnOrderItem.size() == 0)
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单明细不存在");

        //拒绝退货

        //更新退货订单信息 order_cancel
        jdbcTemplate.update("UPDATE " + Tables.ErpSalesOrderRefund + " SET status=? WHERE id =?", EnumErpSalesOrderRefundStatus.Refuse.getIndex(), refundId);

        /**********循环更新退货商品信息 ***********/
        for (var ritem : returnOrderItem) {
            //更新退货商品信息、已退款数量
            jdbcTemplate.update("UPDATE " + Tables.ErpSalesOrderItem + " SET refundId='',refundCount=refundCount-? WHERE id =?", ritem.getQuantity(), ritem.getOrderItemId());
        }

        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }
    /**
     * 系统下单
     * @param salesOrder
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> editSalesOrder(ErpSalesOrderDetailVo salesOrder){
       // log.info("调用系统下单。。。。。。。。。。。"+ JSON.toJSONString(salesOrder));
        Long salesOrderId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT id from erp_sales_order where orderNum=? and shopId=?),0) id",Long.class,salesOrder.getOrderNum(),salesOrder.getShopId());
        StringBuilder insertSQL = new StringBuilder();
        insertSQL.append("INSERT INTO ").append(Tables.ErpSalesOrder);
        insertSQL.append(" set  orderNum=?,");
        insertSQL.append(" goodsCount=?,");
        insertSQL.append(" goodsSpecCount=?,");
        insertSQL.append(" goodsTotalAmount=?,");
        insertSQL.append(" shippingFee=?,");
        insertSQL.append(" totalAmount=?,");
        insertSQL.append(" contactPerson=?,");
        insertSQL.append(" contactMobile=?,");
        insertSQL.append(" province=?,");
        insertSQL.append(" city=?,");
        insertSQL.append(" area=?,");
        insertSQL.append(" address=?,");
        insertSQL.append(" saleType=?,");
        insertSQL.append(" status=?,");
        insertSQL.append(" createBy=?,");
        insertSQL.append(" developerId=?,");
        insertSQL.append(" source=?,");
        insertSQL.append(" shopId=?,");
        insertSQL.append(" buyerUserId=?,");
        insertSQL.append(" payMethod=?,");
        insertSQL.append(" payStatus=?,");
        insertSQL.append(" payTime=?,");
        insertSQL.append(" payAmount=?,");
        insertSQL.append(" orderDate=?,");
        insertSQL.append(" buyerFeedback=?,");
        insertSQL.append(" createOn=?, ");
        insertSQL.append(" sellerMemo=?, ");
        insertSQL.append(" orderTime=?, ");
        insertSQL.append(" buyerName=? ");
        try {
            if(salesOrderId.intValue()==0){
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(insertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1,salesOrder.getOrderNum());
                        ps.setLong(2,salesOrder.getGoodsCount());
                        ps.setLong(3,salesOrder.getGoodsSpecCount());
                        ps.setBigDecimal(4,salesOrder.getGoodsTotalAmount());
                        ps.setBigDecimal(5,salesOrder.getShippingFee());
                        ps.setBigDecimal(6,salesOrder.getTotalAmount());
                        ps.setString(7,salesOrder.getContactPerson());
                        ps.setString(8,salesOrder.getContactMobile());
                        ps.setString(9,salesOrder.getProvince());
                        ps.setString(10,salesOrder.getCity());
                        ps.setString(11,salesOrder.getArea());
                        ps.setString(12,salesOrder.getAddress());
                        ps.setInt(13,salesOrder.getSaleType());
                        ps.setInt(14,salesOrder.getStatus());
                        ps.setString(15,salesOrder.getCreateBy());
                        ps.setInt(16,0);
                        ps.setString(17,salesOrder.getSource());
                        ps.setInt(18,salesOrder.getShopId());
                        ps.setInt(19,salesOrder.getBuyerUserId());
                        ps.setInt(20,salesOrder.getPayMethod());
                        ps.setInt(21,salesOrder.getPayStatus());
                        ps.setLong(22,salesOrder.getPayTime());
                        ps.setBigDecimal(23,salesOrder.getPayAmount());
                        ps.setString(24,salesOrder.getOrderDate());
                        ps.setString(25,salesOrder.getBuyerFeedback());
                        ps.setLong(26,salesOrder.getCreateOn());
                        ps.setString(27,salesOrder.getSellerMemo());
                        ps.setLong(28,salesOrder.getOrderTime());
                        ps.setString(29,salesOrder.getBuyerName());
                        return ps;
                    }
                }, keyHolder);

                Long orderId = keyHolder.getKey().longValue();
                //添加订单明细
                String itemSQL = "INSERT INTO " + Tables.ErpSalesOrderItem + " (orderId,itemAmount,goodsId,goodsTitle,goodsNumber,goodsImage,skuInfo,specId,specNumber,price,quantity,discountPrice,color,size,originOrderItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                //String orderItemIdSQL="SELECT IFNULL((select id from order_item where order_id=(SELECT id FROM orders WHERE order_num=?) AND spec_id=? LIMIT 1),0) id";
                for (var item : salesOrder.getItems()) {
                    //原订单itemid
                    //Long originOrderItemId = jdbcTemplate.queryForObject(orderItemIdSQL,Long.class,salesOrder.getOrderNum(),item.getSpecId());
                    //商品价格
                    double price= item.getDiscountPrice().doubleValue()>0 ? item.getDiscountPrice().doubleValue() : item.getPrice().doubleValue();
                    //商品规格总价
                    double itemAmount = price * item.getQuantity();
                    Integer specId=StringUtils.isEmpty(item.getSpecId()) ? 0 : item.getSpecId();
                    jdbcTemplate.update(itemSQL, orderId, itemAmount, item.getGoodsId(),item.getGoodsTitle(),item.getGoodsNumber(),item.getGoodsImage(),
                            item.getSkuInfo(),specId, item.getSpecNumber(), item.getPrice(), item.getQuantity(), item.getDiscountPrice(),item.getColor(),item.getSize(),0);
                }

            }else jdbcTemplate.update("update erp_sales_order set status=? where id=?",salesOrder.getStatus(),salesOrderId);

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "添加订单失败："+e.getMessage());
        }
    }

    /**
     * 添加订单更新日志
     * @param startTime
     * @param endTime
     * @param shopId
     */
    public void addErpSalesPullOrderLog(Long startTime,Long endTime,Integer shopId,Integer addCount,Integer failCount,Integer updCount,Integer type){
        String addSalesPullLogSQL="INSERT INTO erp_pull_order_log (startTime,endTime,shopId,addCount,failCount,updCount,type,createOn) VALUE (?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(addSalesPullLogSQL,startTime,endTime,shopId,addCount,failCount,updCount,type,System.currentTimeMillis()/1000);
    }
    /**
     * 查询订单更新记录日志
     * @param shopId
     * @return
     */
    public ErpPullOrderLogEntity getErpOrderPullLogByShopId(Integer shopId,Integer type){
        try {
            String erpPullOrderLogSQL="select l.*,d.`name` shopName from erp_pull_order_log l LEFT JOIN dc_shop d ON l.shopId=d.id where l.shopId=? and l.type=? ORDER BY l.endTime desc LIMIT 1";
            return jdbcTemplate.queryForObject(erpPullOrderLogSQL,new BeanPropertyRowMapper<>(ErpPullOrderLogEntity.class),shopId,type);
        }catch (Exception e){
            return null;
        }
    }
    /**
     * 修改关联订单无商品Id
     * @param erpSalesOrderItemId
     * @param erpGoodSpecId
     * @return
     */
    @Transactional
    public ResultVo<Long> updErpSalesOrderSpec(Long erpSalesOrderItemId,Integer erpGoodSpecId,Integer quantity){
        if(erpSalesOrderItemId==null || erpSalesOrderItemId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderItemId");
        }

        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，quantity");
        }

        try {
            //查询orderItem
            var salesOrderSpec = jdbcTemplate.queryForObject("select * from "+Tables.ErpSalesOrderItem+" where id=?",new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class),erpSalesOrderItemId);

            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpOrderSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            if(salesOrderSpec.getGoodsId().intValue()==0){
                String updSalesOrderSpecSQL= "update "+Tables.ErpSalesOrderItem+" set goodsId=?,goodsNumber=?,goodsTitle=?,color=?,size=?,specId=?,specNumber=?,skuInfo=?,quantity=?,modifySkuRemark=?,price=?,discountPrice=? where id=? ";

                String skuInfo="颜色:"+erpOrderSpec.getColorValue()+"尺码:"+erpOrderSpec.getSizeValue();
                String remark = "修改SKU，原SKU为null。";
                BigDecimal price = salesOrderSpec.getPrice();
                BigDecimal discountPrice = salesOrderSpec.getDiscountPrice();
                if(salesOrderSpec.getQuantity().intValue()!=quantity){
                    remark += "数量由："+salesOrderSpec.getQuantity()+"变更为："+quantity;
                    //重新计算单价
                    price = salesOrderSpec.getItemAmount().divide(BigDecimal.valueOf(quantity));
                    discountPrice = salesOrderSpec.getItemAmount().divide(BigDecimal.valueOf(quantity));
                }

                //修改orderItem数据
                jdbcTemplate.update(updSalesOrderSpecSQL,erpOrderSpec.getGoodsId(),erpOrderSpec.getGoodsNumber(),erpOrderSpec.getGoodTitle(),erpOrderSpec.getColorValue(),erpOrderSpec.getSizeValue(),erpOrderSpec.getId(),erpOrderSpec.getSpecNumber(),
                        skuInfo,quantity,remark,price,discountPrice
                        ,erpSalesOrderItemId
                );
            }
            return new ResultVo<>(EnumResultVo.SUCCESS,"成功",salesOrderSpec.getOrderId());
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail,"修改异常:"+e.getMessage());
        }
    }
    /**
     * 添加待处理通知消息
     * @param jsonStr
     * @param shopId
     */
    public void addNotifyMsg(String jsonStr,Integer shopId){
        String erpSalesOrderNotifySQL="insert erp_sales_order_notify set notifyStr=?,shopId=?";
        jdbcTemplate.update(erpSalesOrderNotifySQL,jsonStr,shopId);
    }


    /**
     * 添加礼品
     * @param orderId
     * @param erpGoodSpecId
     * @param quantity
     */
    @Transactional
    public void addGift(Long orderId,Integer erpGoodsId, Integer erpGoodSpecId, Integer quantity) {
        try{
            var gift = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpSalesOrderItem +" WHERE orderId=? and isGift=1 and specId=? ",new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class),orderId,erpGoodSpecId);
            //存在，更新数量
            jdbcTemplate.update("UPDATE "+Tables.ErpSalesOrderItem+" SET quantity=? WHERE id=?",quantity,gift.getId());
        }catch (Exception e){
            try {
                var goods = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoods + " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsEntity.class), erpGoodsId);
                var spec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), erpGoodSpecId);

                //不存在，添加
                String sql = "INSERT INTO " + Tables.ErpSalesOrderItem + " (orderId,itemAmount,goodsId,goodsNumber,goodsTitle,goodsImage,skuInfo,color,size,specId,specNumber,quantity,price,discountPrice,isGift) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                jdbcTemplate.update(sql,orderId,0.00,erpGoodsId,goods.getNumber(),goods.getName(),goods.getImage(),"",spec.getColorValue(),spec.getSizeValue(),erpGoodSpecId,spec.getSpecNumber(),quantity,0.00,0.00,1);
            }catch (Exception ee){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            }
        }

    }

    /**
     * 删除订单礼品
     * @param orderId
     * @param orderItemId
     * @param erpGoodSpecId
     */
    public void deleteGift(Long orderId, Long orderItemId, Integer erpGoodSpecId) {
        jdbcTemplate.update("delete from "+Tables.ErpSalesOrderItem+" WHERE isGift=1 and orderId=? and specId=? ");
    }
    /**
     * 编号与店铺id查询订单id
     * @param orderNum
     * @param shopId
     * @return
     */
    public Long getErpSalesOrderId(String orderNum,Integer shopId){
        try {
            return jdbcTemplate.queryForObject("select id from erp_sales_order where orderNum=? and shopId=?",Long.class,orderNum,shopId);
        }catch (Exception e){
            return 0L;
        }
    }

    /**
     * 订单导入
     * @param orders
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Long> importErpSalesorder(List<ErpSalesOrderDetailVo>  orders){
        log.info("批量订单导入importErpSalesorder");
        StringBuilder insertSQL = new StringBuilder();
        insertSQL.append("INSERT INTO ").append(Tables.ErpSalesOrder);
        insertSQL.append(" set  orderNum=?,");
        insertSQL.append(" goodsCount=?,");
        insertSQL.append(" goodsSpecCount=?,");
        insertSQL.append(" goodsTotalAmount=?,");
        insertSQL.append(" shippingFee=?,");
        insertSQL.append(" totalAmount=?,");
        insertSQL.append(" saleType=?,");
        insertSQL.append(" status=?,");
        insertSQL.append(" createBy=?,");
        insertSQL.append(" developerId=?,");
        insertSQL.append(" source=?,");
        insertSQL.append(" shopId=?,");
        insertSQL.append(" buyerUserId=?,");
        insertSQL.append(" payMethod=?,");
        insertSQL.append(" payStatus=?,");
        insertSQL.append(" payTime=?,");
        insertSQL.append(" payAmount=?,");
        insertSQL.append(" orderDate=?,");
        insertSQL.append(" buyerFeedback=?,");
        insertSQL.append(" createOn=?, ");
        insertSQL.append(" sellerMemo=?, ");
        insertSQL.append(" orderTime=?, ");
        insertSQL.append(" buyerName=?, ");
        insertSQL.append(" contactPerson=?,");
        insertSQL.append(" contactMobile=?,");
        insertSQL.append(" province=?,");
        insertSQL.append(" city=?,");
        insertSQL.append(" area=?,");
        insertSQL.append(" address=?, ");
        insertSQL.append(" logisticsCompany=?,");
        insertSQL.append(" logisticsCode=? ");
        try {
            for(var order:orders){
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(insertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1,order.getOrderNum());
                        ps.setLong(2,order.getGoodsCount());
                        ps.setLong(3,order.getGoodsSpecCount());
                        ps.setBigDecimal(4,order.getGoodsTotalAmount());
                        ps.setBigDecimal(5,order.getShippingFee());
                        ps.setBigDecimal(6,order.getTotalAmount());
                        ps.setInt(7,1);
                        ps.setInt(8,order.getStatus());//1:待审核,2:待发货3:已发货,4:已收货,5:已完成,6已退货
                        ps.setString(9,"");
                        ps.setInt(10,order.getDeveloperId());
                        ps.setString(11,order.getSource());
                        ps.setInt(12,order.getShopId());
                        ps.setInt(13,order.getBuyerUserId());
                        ps.setInt(14,0);
                        ps.setInt(15,2);//支付状态0未付款1部分付款2完全付款
                        ps.setLong(16,0);
                        ps.setBigDecimal(17,order.getPayAmount());
                        ps.setString(18, order.getOrderDate());
                        ps.setString(19,"");
                        ps.setLong(20,System.currentTimeMillis() / 1000);
                        ps.setString(21,"");//系统下单只有卖家备注
                        ps.setLong(22,System.currentTimeMillis() / 1000);
                        ps.setString(23,"");
                        ps.setString(24,order.getContactPerson());
                        ps.setString(25,order.getContactMobile());
                        ps.setString(26,order.getProvince());
                        ps.setString(27,order.getCity());
                        ps.setString(28,order.getArea());
                        ps.setString(29,order.getAddress());
                        ps.setString(30,order.getLogisticsCompany());
                        ps.setString(31,order.getLogisticsCode());
                        return ps;
                    }
                }, keyHolder);

                Long orderId = keyHolder.getKey().longValue();
                //添加订单明细
                String itemSQL = "INSERT INTO " + Tables.ErpSalesOrderItem + " (orderId,itemAmount,goodsId,goodsTitle,goodsNumber,goodsImage,skuInfo,specId,specNumber,price,quantity,discountPrice,color,size) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                for (var item : order.getItems()) {
                    //商品规格总价
                    double itemAmount = item.getPrice().doubleValue() * item.getQuantity();
                    jdbcTemplate.update(itemSQL, orderId, itemAmount, item.getGoodsId(),item.getGoodsTitle(),item.getGoodsNumber(),item.getGoodsImage(),
                            item.getColor()+" "+item.getSize(),item.getSpecId(), item.getSpecNumber(), item.getPrice(), item.getQuantity(), item.getPrice(),item.getColor(),item.getSize());
                }
            }
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "添加订单失败："+e.getMessage());
        }
    }

    /**
     * 店铺销售订单查询
     * @param pageIndex
     * @param pageSize
     * @param shopId
     * @param orderNum
     * @param startTime
     * @param endTime
     * @return
     */
    public PagingResponse<ErpSalesOrderDetailVo> erpSalesOrderDetails(int pageIndex, int pageSize,Integer shopId, String orderNum,Integer startTime, Integer endTime) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS o.* ");
        sb.append(" FROM ").append(Tables.ErpSalesOrder).append(" as o ");
        sb.append(" WHERE 1=1 ");

        List<Object> params = new ArrayList<>();
        if (shopId != null && shopId>0) {
            sb.append(" AND o.shopId=? ");
            params.add(shopId);
        }
        if (startTime != null && startTime > 0) {
            sb.append(" AND o.createOn >= ? ");
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sb.append(" AND o.createOn <= ? ");
            params.add(endTime);
        }
        if (StringUtils.isEmpty(orderNum) == false) {
            sb.append(" AND o.orderNum=? ");
            params.add(orderNum);
        }

        sb.append(" ORDER BY o.id DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpSalesOrderDetailVo.class), params.toArray(new Object[params.size()]));
        list.forEach(i->i.setItems(jdbcTemplate.query("select * from erp_sales_order_item where orderId=? ", new BeanPropertyRowMapper<>(ErpSalesOrderItemEntity.class),i.getId())));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }
}
